package cn.xhf.export.core;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import cn.xhf.export.model.IndexModel;

public class ExcelCore {
	public File initExcel (Map<String, String> headData, String sql) {
	     File file = new File(ExcelExportUtil.getTitle());
		// 创建工作�?
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		// sheet:�?张表的简�?
		// row:表里的行
		// 创建工作薄中的工作表
		HSSFSheet hssfSheet = hssfWorkbook.createSheet();
		hssfSheet.setDefaultColumnWidth(20);
		// 创建�?
		HSSFRow row = hssfSheet.createRow(0);
		// 创建单元格，设置表头 创建�?
		HSSFCell cell = null;
		// 初始化索�?
		int rowIndex = 0;
		int cellIndex = 0;

		// 创建标题�?
		row = hssfSheet.createRow(rowIndex);
		rowIndex++;
		// 遍历标题
		for (String h : headData.keySet()) {
			
			//创建�?
			cell = row.createCell(cellIndex);
			//索引递增
			cellIndex++;
			//逐列插入标题
			cell.setCellValue(headData.get(h));
		}

		// 得到�?有记�? 行：�?
		List<Record> list = Db.find(sql);
		Record record = null;

		if (list != null) {
			// 获取�?有的记录 有多少条记录就创建多少行
			for (int i = 0; i < list.size(); i++) {
				row = hssfSheet.createRow(rowIndex);
				// 得到�?有的�? �?个record就代�? �?�?
				record = list.get(i);
				//下一行索�?
				rowIndex++;
				//刷新新行索引
				cellIndex = 0;
				// 在有�?有的记录基础之上，便利传入进来的表头,再创建N�?
				for (String h : headData.keySet()) {
					cell = row.createCell(cellIndex);
					cellIndex++;
					//按照每条记录匹配数据
					cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
				}
			}
		}
		try {
			FileOutputStream fileOutputStreane = new FileOutputStream(file);
			hssfWorkbook.write(fileOutputStreane);
			fileOutputStreane.flush();
			fileOutputStreane.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return file;
	}

	/**
	 * @param headDataJoin
	 * @param sqlJoin
	 * @return File
	 * 多页脚导出
	 */
	@SuppressWarnings("resource")
	public File MuchExcel(List<Map<String, String>> headDataJoin, List<String> sqlJoin) {
		// TODO Auto-generated method stub
		 File file = new File(ExcelExportUtil.getTitle());
			// 创建工作�?
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
			// sheet:�?张表的简�?
			// row:表里的行
			// 创建工作薄中的工作表
			for (int much = 0; much < headDataJoin.size(); much++) {
				HSSFSheet hssfSheet = hssfWorkbook.createSheet();
				
				hssfSheet.setDefaultColumnWidth(20);
				// 创建�?
				HSSFRow row = hssfSheet.createRow(0);
				// 创建单元格，设置表头 创建�?
				HSSFCell cell = null;
				// 初始化索�?
				int rowIndex = 0;
				int cellIndex = 0;
	
				// 创建标题�?
				row = hssfSheet.createRow(rowIndex);
				rowIndex++;
				// 遍历标题
				for (String h : headDataJoin.get(much).keySet()) {
					
					//创建�?
					cell = row.createCell(cellIndex);
					//索引递增
					cellIndex++;
					//逐列插入标题
					cell.setCellValue(headDataJoin.get(much).get(h));
				}
	
				// 得到�?有记�? 行：�?
				List<Record> list = Db.find(sqlJoin.get(much));
				Record record = null;
	
				if (list != null) {
					// 获取�?有的记录 有多少条记录就创建多少行
					for (int i = 0; i < list.size(); i++) {
						row = hssfSheet.createRow(rowIndex);
						// 得到�?有的�? �?个record就代�? �?�?
						record = list.get(i);
						//下一行索�?
						rowIndex++;
						//刷新新行索引
						cellIndex = 0;
						// 在有�?有的记录基础之上，便利传入进来的表头,再创建N�?
						for (String h : headDataJoin.get(much).keySet()) {
							cell = row.createCell(cellIndex);
							cellIndex++;
							//按照每条记录匹配数据
							cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
						}
					}
				}
			}
			try {
				FileOutputStream fileOutputStreane = new FileOutputStream(file);
				hssfWorkbook.write(fileOutputStreane);
				fileOutputStreane.flush();
				fileOutputStreane.close();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			return file;
	}

	@SuppressWarnings("resource")
	public File initExcel(IndexModel index) {
		// TODO Auto-generated method stub
		 File file = new File(ExcelExportUtil.getTitle());
			// 创建工作�?
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
			// sheet:�?张表的简�?
			// row:表里的行
			// 创建工作薄中的工作表
			HSSFSheet hssfSheet = hssfWorkbook.createSheet(index.getSheetName());
			HSSFCellStyle style = hssfWorkbook.createCellStyle();
			//	一、设置背景色：
			//设置背景
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			style.setFillForegroundColor(IndexedColors.LIME.index);
			style.setAlignment(HorizontalAlignment.CENTER);//水平居中
			style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
			//设置字体
			HSSFFont font = hssfWorkbook.createFont();
			font.setFontName("微软雅黑");//设置字体名称
			font.setFontHeightInPoints((short)10);//设置字号
			style.setFont(font);
			
			HSSFCellStyle dataStyle = hssfWorkbook.createCellStyle();
			//设置背景
			dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			dataStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
			dataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
			dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
			dataStyle.setFont(font);
			
			HSSFCellStyle duubleDataStyle = hssfWorkbook.createCellStyle();
			//设置背景
			duubleDataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			duubleDataStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);
			duubleDataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
			duubleDataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
			duubleDataStyle.setFont(font);

			
//			style.setWrapText(true);   
			hssfSheet.setDefaultColumnWidth(20);
//			hssfSheet.setDefaultRowHeight((short) 20);

			// 创建�?
			HSSFRow row = hssfSheet.createRow(0);
			// 创建单元格，设置表头 创建�?
			HSSFCell cell = null;
			// 初始化索�?
			int rowIndex = 0;
			int cellIndex = 0;

			// 创建标题�?
			row = hssfSheet.createRow(rowIndex);
			rowIndex++;
			// 遍历标题
			for (String h : index.getHead().keySet()) {
				
				//创建�?
				cell = row.createCell(cellIndex);
				//索引递增
				cellIndex++;
				//逐列插入标题
				cell.setCellValue(index.getHead().get(h));
				//		表头		
				cell.setCellStyle(style);
			}

			// 得到�?有记�? 行：�?
			List<Record> list = Db.find(index.getSql());
			Record record = null;

			if (list != null) {
				// 获取�?有的记录 有多少条记录就创建多少行
				for (int i = 0; i < list.size(); i++) {
					row = hssfSheet.createRow(rowIndex);
					// 得到�?有的�? �?个record就代�? �?�?
					record = list.get(i);
					//下一行索�?
					rowIndex++;
					//刷新新行索引
					cellIndex = 0;
					// 在有�?有的记录基础之上，便利传入进来的表头,再创建N�?
					for (String h : index.getHead().keySet()) {
						cell = row.createCell(cellIndex);
						cellIndex++;
						//按照每条记录匹配数据
						cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
						if(i == list.size()-1) {
							cell.setCellStyle(duubleDataStyle);
						}else {
							cell.setCellStyle(dataStyle);
						}
					}
				}
			}
			try {
				FileOutputStream fileOutputStreane = new FileOutputStream(file);
				hssfWorkbook.write(fileOutputStreane);
				fileOutputStreane.flush();
				fileOutputStreane.close();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			return file;
	}

	public File initDataExcel(IndexModel index) {
		// TODO Auto-generated method stub
				 File file = new File(ExcelExportUtil.getTitle());
					// 创建工作�?
					HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
					// sheet:�?张表的简�?
					// row:表里的行
					// 创建工作薄中的工作表
					HSSFSheet hssfSheet = hssfWorkbook.createSheet(index.getSheetName());
					HSSFCellStyle style = hssfWorkbook.createCellStyle();
					//	一、设置背景色：
					//设置背景
					style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
					style.setFillForegroundColor(IndexedColors.LIME.index);
					style.setAlignment(HorizontalAlignment.CENTER);//水平居中
					style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
					//设置字体
					HSSFFont font = hssfWorkbook.createFont();
					font.setFontName("微软雅黑");//设置字体名称
					font.setFontHeightInPoints((short)10);//设置字号
					style.setFont(font);
					
					HSSFCellStyle dataStyle = hssfWorkbook.createCellStyle();
					//设置背景
					dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
					dataStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
					dataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
					dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
					dataStyle.setFont(font);
					
					HSSFCellStyle duubleDataStyle = hssfWorkbook.createCellStyle();
					//设置背景
					duubleDataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
					duubleDataStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);
					duubleDataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
					duubleDataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
					duubleDataStyle.setFont(font);

					
//					style.setWrapText(true);   
					hssfSheet.setDefaultColumnWidth(20);
//					hssfSheet.setDefaultRowHeight((short) 20);

					// 创建�?
					HSSFRow row = hssfSheet.createRow(0);
					// 创建单元格，设置表头 创建�?
					// 初始化索�?
					int rowIndex = 0;
					int cellIndex = 0;

					// 创建标题�?
					row = hssfSheet.createRow(rowIndex);
					List record = new ArrayList();
					HSSFCell cell = null;

//					// 遍历标题
					for (String h : index.getSqlDATA().keySet()) {
						//创建�?
//						cell = row.createCell(cellIndex);
						//索引递增
//						cellIndex++;
						//逐列插入标题
//						cell.setCellValue(h);
						//		表头		
//						cell.setCellStyle(style);
					}
					// 公告
					cell = row.createCell(0);
					cell.setCellValue("抱歉改项目停止维护");
					cell = row.createCell(1);
					cell.setCellValue("作者：羡鸿飞");

					
					try {
						FileOutputStream fileOutputStreane = new FileOutputStream(file);
						hssfWorkbook.write(fileOutputStreane);
						fileOutputStreane.flush();
						fileOutputStreane.close();
					} catch (FileNotFoundException e) {
						e.printStackTrace();
					} catch (IOException e) {
						e.printStackTrace();
					}
					return file;
	}
}
